﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class FieldDutyDAL : IFieldDutyDAL
    {
         IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

         public FieldDutyDAL()
         { 
         
         }         
         //public FieldDutyInfo GetFieldDutyInfo(Nullable<int> seq)
         //{
         //    string strSQL;
         //    FieldDutyInfo info = new FieldDutyInfo();

         //    try
         //    {
         //        dbManager.Open();
         //        dbManager.CreateParameters(2);

         //        dbManager.AddParameters(0, "@seq", seq);
         //        dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

         //        strSQL = "SELECT pd.FIELD_DUTY_SEQ, pd.FIELD_POS_SEQ, pd.CLASS_SEQ, f.POS_TYPE_SEQ"
         //                + ", f.FIELD_POS_NAME, c.CLASS_NAME, pt.POS_TYPE_NAME, pd.DESCR, pd.REMARKS"
         //            + " FROM HRM_MP_FIELD_DUTIES pd"
         //                + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON pd.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
         //                + " LEFT JOIN HRM_MP_CLASSES c ON pd.CLASS_SEQ = c.CLASS_SEQ"
         //                + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
         //            + " WHERE pd.RECORD_STATUS not like @DeleteRecord"
         //                + " AND pd.FIELD_DUTY_SEQ = @seq";

         //        IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

         //        while (rdr.Read())
         //        {
         //            info.FieldDutySeq = rdr.IsDBNull(0) ? (int?)null : rdr.GetInt32(0);
         //            info.FieldPosSeq = rdr.IsDBNull(1) ? (int?)null : rdr.GetInt32(1);
         //            info.ClassSeq = rdr.IsDBNull(2) ? (int?)null : rdr.GetInt32(2);
         //            info.PositionTypeSeq = rdr.IsDBNull(3) ? (int?)null : rdr.GetInt32(3);
         //            info.FieldName = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
         //            info.ClassName = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
         //            info.PositionTypeName = rdr.IsDBNull(6) ? "" : rdr.GetString(6);
         //            info.Descr = rdr.IsDBNull(7) ? "" : rdr.GetString(7);
         //            info.Remarks = rdr.IsDBNull(8) ? "" : rdr.GetString(8);
         //        }

         //        return info;
         //    }
         //    catch
         //    {
         //        return null;
         //        throw;
         //    }
         //    finally
         //    {
         //        dbManager.CloseReader();
         //        dbManager.Dispose();
         //    }
         //}

         public IList<FieldDutyInfo> ListFieldDuties(FieldDutyInfo dutyInfo)
         {
             string strSQL;
             int FieldSeq = 0;
             if (dutyInfo.Field != null)
                 FieldSeq = dutyInfo.Field.Sequence;
             int ClassSeq = 0;
             if (dutyInfo.Class != null)
                 ClassSeq = dutyInfo.Class.Sequence;

             List<FieldDutyInfo> infoList = new List<FieldDutyInfo>();

             try
             {
                 dbManager.Open();
                 dbManager.CreateParameters(5);

                 dbManager.AddParameters(0, "@Sequence", dutyInfo.Sequence);
                 dbManager.AddParameters(1, "@EmpTypeSeq", "1");
                 dbManager.AddParameters(2, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                 dbManager.AddParameters(3, "@FieldSeq", FieldSeq);
                 dbManager.AddParameters(4, "@ClassSeq", ClassSeq);

                 strSQL = "SELECT pd.FIELD_DUTY_SEQ, pd.FIELD_POS_SEQ, pd.CLASS_SEQ, pd.DESCR"
                         + ", pd.REMARKS, pd.RECORD_STATUS, pd.CREATED_BY, pd.UPDATED_BY"
                     + " FROM HRM_MP_FIELD_DUTIES pd"
                         + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON pd.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                         //+ " LEFT JOIN HRM_MP_CLASSES c ON pd.CLASS_SEQ = c.CLASS_SEQ"
                         //+ " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                     + " WHERE pd.RECORD_STATUS = @ActiveRecord"
                     + (dutyInfo.Sequence > 0 ? " AND pd.FIELD_DUTY_SEQ = @Sequence" : "" )
                     + (FieldSeq > 0 ? " AND pd.FIELD_POS_SEQ = @FieldSeq" : "")
                     + (ClassSeq > 0 ? " AND pd.CLASS_SEQ = @ClassSeq" : "")
                         + " AND f.EMP_TYPE_SEQ = @EmpTypeSeq";

                 IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    FieldDAL fieldDAL = new FieldDAL();
                    FieldInfo fieldInfo = new FieldInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        IList<FieldInfo> fieldList = fieldDAL.ListFields(new FieldInfo(rdr.GetInt32(1)));
                        fieldInfo = fieldList[0];
                    }

                    ClassDAL cDAL = new ClassDAL();
                    ClassInfo cInfo = new ClassInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<ClassInfo> cList = cDAL.ListClasses(new ClassInfo(rdr.GetInt32(2)));
                        cInfo = cList[0];
                    }

                     FieldDutyInfo info = new FieldDutyInfo(rdr.GetInt32(0)
                        , fieldInfo
                        , cInfo
                        , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                        , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                        , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                        , rdr.IsDBNull(6) ? (int?)null : rdr.GetInt32(6)
                        , rdr.IsDBNull(7) ? (int?)null : rdr.GetInt32(7));

                     infoList.Add(info);
                 }
             }
             catch (Exception ex)
             {
                 throw (ex);
             }
             finally
             {
                 dbManager.CloseReader();
                 dbManager.Dispose();
             }
             return infoList;
         }
        // public DataSet LoadGridFieldPositionDuties(int EmpTypeSeq)
        //{
        //    string strSQL;

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
        //        dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

        //        strSQL = "SELECT pd.FIELD_DUTY_SEQ, pd.FIELD_POS_SEQ, pd.CLASS_SEQ, pd.DESCR"
        //                + ", pd.REMARKS, f.FIELD_POS_NAME, c.CLASS_NAME"
        //                + ", f.POS_TYPE_SEQ, pt.POS_TYPE_NAME"
        //            + " FROM HRM_MP_FIELD_DUTIES pd"
        //                + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON pd.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
        //                + " LEFT JOIN HRM_MP_CLASSES c ON pd.CLASS_SEQ = c.CLASS_SEQ"
        //                + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
        //            + " WHERE pd.RECORD_STATUS not like @DeleteRecord"
        //                + " AND f.EMP_TYPE_SEQ = @EmpTypeSeq";

        //        return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
        //    }
        //    catch
        //    {
        //        return null;
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        public void Insert(FieldDutyInfo fieldDutyInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@FieldPosSeq", fieldDutyInfo.Field == null ? (int?)null : fieldDutyInfo.Field.Sequence);
                dbManager.AddParameters(1, "@ClassSeq", fieldDutyInfo.Class == null ? (int?)null : fieldDutyInfo.Class.Sequence);
                dbManager.AddParameters(2, "@Descr", fieldDutyInfo.Description);
                dbManager.AddParameters(3, "@Remarks", fieldDutyInfo.Remarks);
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@CreatedBy", fieldDutyInfo.CreatedBy);
                dbManager.AddParameters(6, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_MP_FIELD_DUTIES(FIELD_POS_SEQ, CLASS_SEQ, DESCR"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@FieldPosSeq, @ClassSeq, @Descr"
                        + ", @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(FieldDutyInfo fieldDutyInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@FiledDutySeq", fieldDutyInfo.Sequence);
                dbManager.AddParameters(1, "@FieldPosSeq", fieldDutyInfo.Field == null ? (int?)null : fieldDutyInfo.Field.Sequence);
                dbManager.AddParameters(2, "@ClassSeq", fieldDutyInfo.Class == null ? (int?)null : fieldDutyInfo.Class.Sequence);
                dbManager.AddParameters(3, "@Descr", fieldDutyInfo.Description);
                dbManager.AddParameters(4, "@Remarks", fieldDutyInfo.Remarks);
                dbManager.AddParameters(5, "@UpdatedBy", fieldDutyInfo.UpdatedBy);
                dbManager.AddParameters(6, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_FIELD_DUTIES"
                           + " SET FIELD_POS_SEQ = @FieldPosSeq"
                                + ", CLASS_SEQ = @ClassSeq"
                                + ", DESCR = @Descr"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE FIELD_DUTY_SEQ = @FiledDutySeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Seq", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_MP_FIELD_DUTIES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE FIELD_DUTY_SEQ = @Seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
